﻿using ciji.Application.Dto;
using ciji.Core;
using Dm.filter;
using Dm.util;
using Furion.DatabaseAccessor;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Security.Cryptography;
using System.Security.Policy;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using Yitter.IdGenerator;
using static Dm.net.buffer.ByteArrayBuffer;

namespace ciji.Application.Data
{
    /// <summary>
    /// 救助项目管理
    /// </summary>
    [ApiDescriptionSettings("救助项目管理", Order =650, Description = "救助项目管理")]
    [LoggingMonitor]
    public class JZDataService : IDynamicApiController
    {

        private SqlSugarRepository<cts_JZGroup> _jzGroupRep;
        private SqlSugarRepository<cts_Area_Year> _areaYearRep;
        private SqlSugarRepository<cts_GroupTown> _groupTownRep;
        private SqlSugarRepository<cts_JZPerson> _jzPersonRep;
        private SqlSugarRepository<cts_JZInfo> _jzInfoRep;
        private SqlSugarRepository<cts_Commodity> _commodityRep;
        private SqlSugarRepository<cts_Employee> _employeeRep;
        private SqlSugarRepository<cts_JZPersonFamily> _personFamilyRep;
        private SqlSugarRepository<cts_JZInComeDetail> _inComeDetailRep;
        private SqlSugarRepository<cts_JZPayDetail> _payDetailRep;

        
        private SqlSugarRepository<cts_JZUpdate> _jzupdateRep;
        private SqlSugarRepository<cts_JZInfoImg> _JZInfoImgRep;
        private SqlSugarRepository<cts_DBDoc> _dbDocRep;




        public JZDataService(SqlSugarRepository<cts_JZGroup> jzGroupRep
            , SqlSugarRepository<cts_Area_Year> areaYearRep
            , SqlSugarRepository<cts_GroupTown> groupTownRep
            , SqlSugarRepository<cts_JZPerson> jzPersonRep
            , SqlSugarRepository<cts_JZInfo> jzInfoRep
            , SqlSugarRepository<cts_Commodity> commodityRep
            
            , SqlSugarRepository<cts_Employee> employeeRep
            ,SqlSugarRepository<cts_JZPersonFamily> personFamilyRep
            ,SqlSugarRepository<cts_JZInComeDetail> inComeDetailRep
            , SqlSugarRepository<cts_JZPayDetail> payDetailRep
            , SqlSugarRepository<cts_JZUpdate> jzupdateRep
            , SqlSugarRepository<cts_JZInfoImg> JZInfoImgRep
            , SqlSugarRepository<cts_DBDoc> dbDocRep)
        {
            _jzGroupRep = jzGroupRep;
            _areaYearRep = areaYearRep;
            _groupTownRep = groupTownRep;
            _jzPersonRep = jzPersonRep;
            _jzInfoRep = jzInfoRep;
            _commodityRep = commodityRep;
            _employeeRep = employeeRep;
            _personFamilyRep = personFamilyRep;
            _inComeDetailRep = inComeDetailRep;
            _payDetailRep = payDetailRep;
            _jzInfoRep = jzInfoRep;
            _JZInfoImgRep = JZInfoImgRep;
            _dbDocRep = dbDocRep;
            _jzupdateRep = jzupdateRep;
        }
        /// <summary>
        /// 保存项目
        /// </summary>
        /// <param name="input"></param>
        [UnitOfWork]
        public void SaveJZGroup(cts_JZGroup input)
        {

            input.Id = YitIdHelper.NextId();
            input.Gid = Guid.NewGuid().ToString();
            _jzGroupRep.Insert(input);


            cts_Area_Year cAy = new cts_Area_Year
            {
                YearName = input.GroupYear,
                AreaName = input.GroupArea,
                Type = 2
            };
            var oldCount = _areaYearRep.AsQueryable().Where(m => m.YearName == cAy.YearName && m.AreaName == cAy.AreaName && m.Type == cAy.Type).Count();
            if (oldCount == 0)
            {
                _areaYearRep.Insert(cAy);
            }

        }
        /// <summary>
        /// 导入救助人员信息
        /// </summary>
        /// <param name="input"></param>
        [UnitOfWork]
        public List<ImportPerson> ImportJZPerson(ImportProp input)
        {

            cts_JZGroup pGroup = input.jGroup;
            List<ImportPerson> listError = new List<ImportPerson>();
            List<ImportPerson> listRepeat = new List<ImportPerson>();
            List<cts_JZPerson> listPerson = new List<cts_JZPerson>();


            if (string.IsNullOrEmpty(pGroup.Gid))
            {
                pGroup.Gid = Guid.NewGuid().ToString();
                pGroup.sys_CreateDate = DateTime.Now;
                _jzGroupRep.Insert(pGroup);
            }
            cts_Area_Year cAy = new cts_Area_Year
            {
                YearName = pGroup.GroupYear,
                AreaName = pGroup.GroupArea,
                Type = 2
            };
            var oldCount = _areaYearRep.AsQueryable().Where(m => m.YearName == cAy.YearName && m.AreaName == cAy.AreaName && m.Type == cAy.Type).Count();
            if (oldCount == 0)
            {
                _areaYearRep.Insert(cAy);
            }
            foreach (var itemDesc in input.descList)
            {
                cts_GroupTown cT = new cts_GroupTown
                {
                    GroupId = pGroup.Gid,
                    TownName = string.IsNullOrEmpty(itemDesc.pSheet) ? "默认" : itemDesc.pSheet,
                    Type = 2
                };


                var oldTownCount = _groupTownRep.AsQueryable().Where(m => m.GroupId == cT.GroupId && m.TownName == cT.TownName).Count();
                if (oldTownCount == 0)
                {
                    _groupTownRep.Insert(cT);
                }
                var orderindex = 0;
                foreach (var item in itemDesc.personList)
                {
                    orderindex += 1;
                    if (string.IsNullOrEmpty(item.姓名))
                    {
                        item.错误信息 = "信息不全，姓名：" + item.姓名 + ",身份证号：" + item.身份证号 + ",地址：" + item.地址 + item.住址;
                        listError.Add(item);
                        continue;
                    }
                    if (string.IsNullOrEmpty(item.身份证号))
                    {
                        item.错误信息 = "信息不全，姓名：" + item.姓名 + ",身份证号：" + item.身份证号 + ",地址：" + item.地址 + item.住址;
                        listError.Add(item);
                        continue;
                    }
                    if (string.IsNullOrEmpty(item.住址) && string.IsNullOrEmpty(item.地址))
                    {
                        item.错误信息 = "信息不全，姓名：" + item.姓名 + ",身份证号：" + item.身份证号 + ",地址：" + item.地址 + item.住址;
                        listError.Add(item);
                        continue;
                    }

                    item.姓名 = item.姓名.Replace("\n", "").Replace("\t", "").Replace("\r", "").Trim();
                    item.身份证号 = item.身份证号.Replace("\n", "").Replace("\t", "").Replace("\r", "").Replace("'", "").Trim();
                    if (item.身份证号.Length != 15 && item.身份证号.Length != 18)
                    {
                        item.错误信息 = "身份证号格式不正确，姓名：" + item.姓名 + ",身份证号：" + item.身份证号 + ",地址：" + item.地址 + item.住址;
                        listError.Add(item);
                        continue;
                    }

                    var model = _jzPersonRep.GetFirst(m => m.IdCardNo == item.身份证号 && m.IsSaved == 0);
                    cts_JZInfo info = null;
                    if (model != null)
                    {
                        info = _jzInfoRep.GetFirst(m => m.DBPersonId == model.Gid);

                    }

                    if (model != null)
                    {
                        var personstate = "未核查";
                        if (info != null)
                        {
                            personstate = "已核查";
                        }

                        item.错误信息 = "'" + item.姓名 + "' 在'" + model.GroupName + "'中，" + personstate + "！";
                        if (model.GroupId == pGroup.Gid)
                        {
                            continue;
                        }
                        listRepeat.Add(item);
                    }
                    cts_JZPerson cp = new cts_JZPerson();
                    cp.Gid = Guid.NewGuid().ToString();
                    cp.Name = item.姓名;
                    if (string.IsNullOrEmpty(item.性别))
                    {
                        cp.Sex = IdCardHelper.GetSexFromIdCard(item.身份证号);
                    }
                    else
                    {
                        cp.Sex = item.性别;
                    }
                    cp.Age = IdCardHelper.GetAgeFromIdCard(item.身份证号);
                    cp.Birthday = IdCardHelper.GetBirthDayFromIdCard(item.身份证号);
                    cp.IdCardNo = item.身份证号;

                    cp.OrderIndex = orderindex;
                    cp.Phone = item.联系方式;
                    cp.Community = item.村居;
                    if (!string.IsNullOrEmpty(item.住址))
                    {
                        cp.Address = item.住址;
                    }
                    else
                    {
                        cp.Address = item.地址;
                    }
                    if (!string.IsNullOrEmpty(item.家庭人口))
                    {
                        cp.FamilyCount = Util.Str2Int(item.家庭人口);
                    }
                    if (!string.IsNullOrEmpty(item.保障人口))
                    {
                        cp.EnsureCount = Util.Str2Int(item.保障人口);
                    }
                    if (!string.IsNullOrEmpty(item.家庭收入))
                    {
                        cp.FamilyIncome = Util.Str2Decimal(item.家庭收入);
                    }
                    if (cp.FamilyIncome > 0 && cp.FamilyCount > 0)
                    {
                        var avg = decimal.Divide((decimal)cp.FamilyIncome, (int)cp.FamilyCount);
                        cp.AvgIncome = decimal.Round(avg, 2);
                    }
                    if (!string.IsNullOrEmpty(item.金融资产))
                    {
                        cp.FinanceAssets = Util.Str2Decimal(item.金融资产);
                    }
                    if (!string.IsNullOrEmpty(item.住房套数))
                    {
                        cp.HouseCount = Util.Str2Int(item.住房套数);
                    }
                    if (!string.IsNullOrEmpty(item.住房面积))
                    {
                        cp.HouseArea = Util.Str2Decimal(item.住房面积);
                    }
                    if (!string.IsNullOrEmpty(item.月保障金))
                    {
                        cp.MonthMoney = Util.Str2Decimal(item.月保障金);
                    }
                    cp.OtherIncome = item.其他财产;
                    cp.PoorReason = item.致贫原因;
                    cp.Town = itemDesc.pSheet;
                    cp.GroupId = pGroup.Gid;
                    cp.GroupName = pGroup.GroupName;
                    listPerson.Add(cp);

                }

            }

            if (listError.Count > 0)
            {
                throw Oops.Bah("操作失败").WithData(listError);
            }
            _jzPersonRep.InsertRange(listPerson);
            return listRepeat;
        }


        /// <summary>
        /// 查询人员分组
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public List<cts_JZGroup> SearchJZGroup(SearchPersonGroupDto input)
        {

            return _jzGroupRep.AsQueryable()
              .Where(m => m.IsSaved == input.issaved )
              .WhereIF(input.type > -1, m => m.Type == input.type)
              .WhereIF(!string.IsNullOrEmpty(input.str), m => m.GroupName.contains(input.str))
              .OrderByDescending(m => m.Id)
              .ToList();
        }
        /// <summary>
        /// 查询人员分组-带分页
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public SqlSugarPagedXList<cts_JZGroup> SearchJZGrouphasPage(SearchPersonGrouphasPageDto input)
        {

            var query = _jzGroupRep.AsQueryable()
                .Where(m => m.sys_IsDelete == 0 && m.IsSaved == input.issaved )
                  .WhereIF(input.type > -1, m => m.Type == input.type)
                .WhereIF(!string.IsNullOrEmpty(input.str), m => m.GroupName.Contains(input.str))
                .WhereIF(!string.IsNullOrEmpty(input.year), m => m.GroupYear == input.year)
                 .WhereIF(!string.IsNullOrEmpty(input.area), m => m.GroupArea == input.area);


            var subQuery = _jzPersonRep.AsQueryable()
                .LeftJoin<cts_JZInfo>((a, b) => a.Gid == b.DBPersonId && a.sys_IsDelete == 0 && b.sys_IsDelete == 0)
                .GroupBy(a => a.GroupId)
                .Select((a, b) => new
                {
                    GroupId = a.GroupId,
                    PersonCount = SqlFunc.AggregateCount(1),
                    PersonCount2 = SqlFunc.AggregateSum(b.Gid != null ? 1 : 0)
                });

            return query.LeftJoin(subQuery, (a, b) => a.Gid == b.GroupId)
                           .Select((a, b) => new cts_JZGroup
                           {
                               // 选择需要的字段
                               Gid = a.Gid.SelectAll(),
                               PersonCount = b.PersonCount,
                               PersonCount2 = b.PersonCount2
                           }).OrderByDescending(a => a.Id).ToPagedList(input.page, input.size);
        }




        //查询人员分组-根据地区查询
        public SqlSugarPagedXList<cts_JZGroup> SearchAreaJZGroup(SearchPersonGrouphasPageDto input)
        {
            if (UserHelper.GetAuth() != (UserHelper.GetAuth() | 4))
            {
                throw Oops.Bah("无权限");
            }


            return _jzGroupRep.AsQueryable()
              .Where(m => m.IsSaved == input.issaved && m.Type == 0)
              .Where(m => m.SavedAreaId == UserHelper.GetAreaId())
              .WhereIF(!string.IsNullOrEmpty(input.str), m => m.GroupName.contains(input.str))
              .WhereIF(!string.IsNullOrEmpty(input.year), m => m.GroupYear.contains(input.year))
              .OrderByDescending(m => m.Id)
              .ToPagedList(input.page, input.size);
        }


        //查询乡镇
        public List<cts_GroupTown> GetJZGroupTown(string groupid)
        {



            return  _jzPersonRep.AsQueryable()
                .LeftJoin<cts_JZInfo>((a, b) => a.Gid == b.DBPersonId)
                .Where(a => a.GroupId == groupid && a.sys_IsDelete == 0)
                .GroupBy(a => a.Town)
                .Select((a, b) => new cts_GroupTown
                {
                    TownName = a.Town,
                    PersonCount = SqlFunc.AggregateCount(1),
                    PersonCount2 = SqlFunc.AggregateSum(b.Gid != null ? 1 : 0)
                }).ToList();
          

            
        }


        /// <summary>
        /// 查询人员分组-根据地区查询
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>

        public SqlSugarPagedXList<cts_JZPerson> SearchJZPerson(SearchPersonDto input)
        { 
            return _jzPersonRep.AsQueryable()
               .LeftJoin<cts_JZInfo>((a, b) => a.Gid == b.DBPersonId) 
               .WhereIF(!string.IsNullOrEmpty(input.GroupId), a => a.GroupId.Equals(input.GroupId))
               .WhereIF(!string.IsNullOrEmpty(input.GroupTown), a => a.Town.Equals(input.GroupTown))
              .WhereIF(input.State == 0, (a, b) => a.State == 0 && b.Id == null)
              .WhereIF(input.State == 1, (a, b) => b.Id != null)
              .WhereIF(input.State == 2, (a, b) => a.State == 2 && b.Id == null)
              .WhereIF(input.State == 3, (a, b) => a.State == 3 && b.Id == null)
              .WhereIF(input.State == 4, a => a.State == 4)
              .WhereIF(input.IsNew == 1, a => a.IsNew == 1)
               .WhereIF(!string.IsNullOrEmpty(input.EmpId), a => a.DBInfo != null&& a.DBInfo.sys_Creater == input.EmpId)
               .WhereIF(!string.IsNullOrEmpty(input.Str), a => a.Name.Contains(input.Str) || a.Community.Contains(input.Str) || a.Address.Contains(input.Str) || a.IdCardNo.Contains(input.Str))
               .WhereIF(UserHelper.GetAuth() != (UserHelper.GetAuth() | 2), (a, b) => b.Id == null || b.sys_Creater == UserHelper.GetUserId())
                .Select((a, b) => new cts_JZPerson
                {
                    Id = a.Id.SelectAll(),
                    DBInfo = b
                }).ToPagedList(input.page, input.size);
        }

        /// <summary>
        /// 保存人员备注
        /// </summary>
        public void SaveJZPersonRemark(SaveRemmarkDto input)
        {
            _jzPersonRep.AsUpdateable()
                .SetColumns(m => new cts_JZPerson { Remark = input.remark, State = input.state })
                .Where(m => m.Gid == input.gid).ExecuteCommand();
        }

        /// <summary>
        /// 保存核查信息结论
        /// </summary>
        public void SaveJZFinalResult(SaveDBFinalResultDto input)
        {

            if (input.type == 2)
            {
                _jzInfoRep.AsUpdateable()
                .SetColumns(m => m.FinalResult2 == input.finalresult)
                .Where(m => m.Gid == input.gid).ExecuteCommand();
            }
            else
            {
                _jzInfoRep.AsUpdateable()
              .SetColumns(m => m.FinalResult == input.finalresult)
              .Where(m => m.Gid == input.gid).ExecuteCommand();
            }
        
        }
        
 


        /// <summary>
        /// 保存救助核查信息
        /// </summary>
        /// <param name="info"></param>
        [UnitOfWork]
        public void SaveJZInfo(cts_JZInfo info)
        {

            var cEmp = _employeeRep.GetFirst(m => m.Gid == UserHelper.GetUserId());
            if (cEmp.Auth != (cEmp.Auth | 1))
            {
                throw Oops.Bah("无权限");
            }
            //新增
            if (string.IsNullOrEmpty(info.Gid))
            {
                var oldCount = _jzInfoRep.AsQueryable().Where(m => m.DBPersonId == info.DBPersonId && m.IsSaved == 0).Count();
                if (oldCount > 0)
                {
                    throw Oops.Bah("该人员已核查");
                }
                info.Gid = Guid.NewGuid().ToString();
                info.EmployeeName1 = cEmp.SignImg1;
                info.EmployeeName2 = cEmp.SignImg2;
                _jzInfoRep.Insert(info);
            }
            else {
                var oldInfo = _jzInfoRep.GetFirst(m => m.Gid == info.Gid);
                if (cEmp.Auth != (cEmp.Auth | 2) && !cEmp.Gid.Equals(oldInfo.sys_Creater))
                {
                    throw Oops.Bah("无权限");
                }
                _jzInfoRep.Update(info);
                _personFamilyRep.Delete(m => m.DBInfoId == info.Gid);
                _inComeDetailRep.Delete(m => m.DBInfoId == info.Gid);
                _payDetailRep.Delete(m => m.DBInfoId == info.Gid);
                //保存修改记录 
                Type type = info.GetType();
                List<cts_JZUpdate> updateLogs = new List<cts_JZUpdate>();
                var seq = Guid.NewGuid().ToString();
                foreach (PropertyInfo p in type.GetProperties())
                {
                    var name = p.Name;
                    if (name.Contains("sys_"))
                    {
                        continue;
                    }
                    string tmpValue = name;

                    cts_JZUpdate uptemp = new cts_JZUpdate();
                    var oldValue = p.GetValue(oldInfo);
                    var newValue = p.GetValue(info);

                    if (oldValue == null)
                    {
                        oldValue = "";
                    }
                    if (newValue == null)
                    {
                        newValue = "";
                    }

                    if (oldValue.GetType() == typeof(int) || oldValue.GetType() == typeof(double) || oldValue.GetType() == typeof(decimal))
                    {
                        if (double.Parse(oldValue.ToString()) == double.Parse(newValue.ToString()))
                        {
                            continue;
                        }
                    }
                    if (!oldValue.ToString().Equals(newValue.ToString()))
                    {
                        if (p.GetType() == typeof(int))
                        {
                            if (oldValue.ToString().Equals("-1"))
                            {
                                uptemp.OldFiled = "";
                            }
                            else
                            {
                                uptemp.OldFiled = oldValue.ToString();
                            }
                        }
                        else
                        {
                            uptemp.OldFiled = oldValue.ToString();
                        }
                        uptemp.DBInfoId = oldInfo.Gid;
                        uptemp.NewFiled = newValue.ToString();
                        uptemp.FileAction = "修改";
                        uptemp.FiledName = tmpValue;
                        uptemp.Type = oldInfo.IsSaved;
                        uptemp.Seq = seq;
                        uptemp.sys_CreateDate = DateTime.Now;
                        uptemp.sys_Creater = cEmp.Gid;
                        uptemp.sys_CreaterName = cEmp.EmployeeName;
                        updateLogs.add(uptemp); 
                      
                    }

                }
                _jzupdateRep.InsertRange(updateLogs);

            }

            var vPerson = _jzPersonRep.GetFirst(m => m.Gid == info.DBPersonId);
            vPerson.Name = info.Name;
            vPerson.Town = info.Town;
            vPerson.Community = info.Community;
            vPerson.Address = info.Address;
            vPerson.Phone = info.Phone;
            vPerson.EnsureCount = info.EnsureCount;
            vPerson.FamilyCount = info.FamilyCount;
            vPerson.State = info.State;
            _jzPersonRep.Update(vPerson);

            cts_GroupTown cT = new cts_GroupTown
            {
                GroupId = vPerson.GroupId,
                TownName = info.Town,
                Type = 2
            };

            var oldTownCount = _groupTownRep.AsQueryable().Where(m => m.GroupId == cT.GroupId && m.TownName == cT.TownName).Count();
            if (oldTownCount == 0)
            {
                _groupTownRep.Insert(cT);
            }

            var familyList = new List<cts_JZPersonFamily>();
            var inComeList = new List<cts_JZInComeDetail>();
            var payList = new List<cts_JZPayDetail>();
            foreach (var item in info.familyperson)
            {
                item.Gid = Guid.NewGuid().ToString();
                item.DBInfoId = info.Gid;
                if (item.Age <= 0)
                {
                    item.Age = IdCardHelper.GetAgeFromIdCard(item.IdCardNo);
                }
                familyList.add(item);


            }
            if (familyList.Count > 0)
            {
                _personFamilyRep.InsertRange(familyList);
            }

            foreach (var item in info.dbincome)
            {
                item.Gid = Guid.NewGuid().ToString();
                item.DBInfoId = info.Gid;
                inComeList.add(item);
            }
            _inComeDetailRep.InsertRange(inComeList);

            foreach (var item in info.dbpay)
            {
                item.Gid = Guid.NewGuid().ToString();
                item.DBInfoId = info.Gid;
                payList.add(item);
            }
            _payDetailRep.InsertRange(payList);


        }
        /// <summary>
        /// 保存录音
        /// </summary>
        /// <param name="input"></param>
        public void UpdateRecording(UpdateRecordingDto input)
        {

            var sqlUpdate = $@"update cts_JZInfo set SoundRecording='{input.SoundRecording}',RecordingTime={input.RecordingTime} where Gid='{input.gid}'";
            _jzGroupRep.AsSugarClient().Ado.ExecuteCommand(sqlUpdate);
        }

        /// <summary>
        /// 添加图片信息
        /// </summary>
        /// <param name="input"></param>
        public void AddJZImg(UploadImgDto input)
        {

            var item = new cts_JZInfoImg
            {
                Gid = Guid.NewGuid().ToString(),
                DBInfoId = input.gid,
                Type = input.type,
                ImgUrl = input.path
            };
            _JZInfoImgRep.Insert(item);


        }


        /// <summary>
        /// 删除图片信息
        /// </summary>
        /// <param name="gid"></param>
        public void DeleteJZImg(string gid)
        {
            _JZInfoImgRep.AsUpdateable().SetColumns(m => m.sys_IsDelete == 1).Where(m => m.Gid == gid).ExecuteCommand();         

        } 
      

        /// <summary>
        /// 根据身份证号查询人员信息、没有就新增
        /// </summary>
        /// <param name="info"></param>
        /// <returns></returns>
        public cts_JZPerson SearchOrSaveJZPerson(cts_JZPerson info)
        {
            var cEmp = _employeeRep.GetFirst(m => m.Gid == UserHelper.GetUserId());

            info.IdCardNo = info.IdCardNo.Trim();
            if (info.IdCardNo.Length != 15 && info.IdCardNo.Length != 18)
            {
                throw Oops.Bah("身份证号格式不正确");
            }
            info.IsNew = 1;
            if (string.IsNullOrEmpty(info.GroupId))
            {
                info.GroupId = AppStr.NEWGROUPID;
                info.GroupName = AppStr.NEWGROUPNAME;
            }


            var model = _jzPersonRep.GetFirst(m => m.IdCardNo == info.IdCardNo && m.GroupId == info.GroupId);
            if (model != null)
            {
                throw Oops.Bah("该项目中此身份证号已存在");
            }
            int orderIndex = _jzPersonRep.AsQueryable()
             .Where(m => m.GroupId == info.GroupId && m.Town == info.Town)
             .Max(m => m.OrderIndex);
            info.OrderIndex = (orderIndex + 1);
            info.Gid = Guid.NewGuid().ToString();
            info.IsNew = 1;
            info.Age = IdCardHelper.GetAgeFromIdCard(info.IdCardNo);
            info.Birthday = IdCardHelper.GetBirthDayFromIdCard(info.IdCardNo);
            info.sys_CreateDate = DateTime.Now;
            info.sys_Creater = cEmp.Gid;
            info.sys_CreaterName = cEmp.EmployeeName;
            _jzPersonRep.Insert(info);
            return info;
        }



        //根据身份证号查询人员是否已被评估
        public bool GetHasJZByIdcard(string idcardno)
        {


            var personList= _jzPersonRep.AsQueryable().InnerJoin<cts_JZInfo>((a, b) => a.Gid == b.DBPersonId)
                  .Where(a => a.IdCardNo == idcardno &&a.IsSaved == 0)
                  .Select((a,b)=>new cts_JZPerson { 
                    Id=a.Id.SelectAll(), 
                    DBInfo=b,
                  }).ToList();
            if (personList.Count>0)
            {
                throw Oops.Bah("该人员已被核查,在'" + personList[0].GroupName + "'中!");
            }
            return true;
            
        }



        //根据Id救助人员信息
        public cts_JZPerson GetJZPersonById(string gid)
        {

            var person = _jzPersonRep.AsQueryable()
                     .LeftJoin<cts_JZInfo>((a, b) => a.Gid == b.DBPersonId)                   
                     .Where((a, b ) => a.Gid == gid)
                     .Select((a, b) => new cts_JZPerson
                     {
                         Id = a.Id.SelectAll(),
                         DBInfo = b

                     }).First();

            if (person.DBInfo != null)
            {
                person.DBInfo.familyperson = _personFamilyRep.GetList(m => m.DBInfoId == person.DBInfo.Gid);
                person.DBInfo.dbimg = _JZInfoImgRep.GetList(m => m.DBInfoId == person.Gid);
                person.DBInfo.dbincome = _inComeDetailRep.GetList(m => m.DBInfoId == person.DBInfo.Gid);
                person.DBInfo.dbpay = _payDetailRep.GetList(m => m.DBInfoId == person.DBInfo.Gid);
            }
            return person;

           
        }


        //根据Id救助核查信息
        public cts_JZInfo GetJZInfoById(string gid)
        {
            var info = _jzInfoRep.GetFirst(m => m.Gid == gid);
            if (info == null)
            {
                throw Oops.Bah("未查询到数据");
            }
            return info;
        }

        /// <summary>
        /// 更新救助签名信息
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public void JZUpdateSignImg(UpdateSignImgInput input)
        { 
            if (input.type == 1)
            {
                _jzInfoRep.AsUpdateable().SetColumns(m => m.CommunitySignImg == input.url).Where(m => m.Gid == input.gid).ExecuteCommand();

            }
            else if (input.type == 2)
            {
                _jzInfoRep.AsUpdateable().SetColumns(m => m.TownSignImg == input.url).Where(m => m.Gid == input.gid).ExecuteCommand();
               
            }
            

        }

        /// <summary>
        /// 转移人员到项目
        /// </summary>
        /// <param name="input"></param>
        public void TransJZPersonGroup(TransPersonInput input)
        {
            var cEmp = _employeeRep.GetFirst(m => m.Gid == UserHelper.GetUserId());
            if (cEmp.Auth != (cEmp.Auth | 1))
            {
                throw Oops.Bah("无权限"); 
            }

            var person = _jzPersonRep.GetFirst(m => m.Gid == input.personid);
            if (person == null)
            {
                throw Oops.Bah("未找到此评估对象");
            }
            person.GroupId = input.groupid;
            person.GroupName = input.groupname;
            person.Town = input.grouptown;

            var orderIndex = _jzPersonRep.AsQueryable()
                                     .Where(m => m.GroupId == person.GroupId && m.Town == person.Town)
                                     .Max(m => m.OrderIndex);
            person.OrderIndex = (orderIndex + 1);
            _jzPersonRep.AsUpdateable(person).UpdateColumns(m => new { 
                m.GroupId,
                m.GroupName,
                m.Town,
                m.OrderIndex,

            }).ExecuteCommand();
            cts_GroupTown cT = new cts_GroupTown
            {
                
                GroupId = person.GroupId,
                TownName = person.Town,
                Type=2
            };
            var town = _groupTownRep.GetFirst(m => m.GroupId == cT.GroupId && m.TownName.Equals(cT.TownName));
            if (town == null)
            {
                _groupTownRep.Insert(cT);
            } 
             
        }

        /// <summary>
        /// 保存核查对象
        /// </summary>
        /// <param name="info"></param>
        [UnitOfWork]
        public void SaveJZPerson(cts_JZPerson info)
        {
            if (info == null)
            {
                throw Oops.Bah("保存对象不能为空");
            }
            if (string.IsNullOrEmpty(info.Name))
            {
                throw Oops.Bah("姓名不能为空");
            }
            if (string.IsNullOrEmpty(info.Sex))
            {
                throw Oops.Bah("姓别不能为空");
            }
            if (string.IsNullOrEmpty(info.IdCardNo))
            {
                throw Oops.Bah("身份证号不能为空");
            }
            if (string.IsNullOrEmpty(info.Address))
            {
                throw Oops.Bah("住址不能为空");
            }

            if (string.IsNullOrEmpty(info.Gid))
            {
                var model = _jzPersonRep.AsQueryable()
           .Where(a => a.IdCardNo == info.IdCardNo && a.IsSaved == 0 && a.GroupId == info.GroupId).First();
                if (model != null)
                {
                    throw Oops.Bah("该项目中此身份证号已存在");
                }
                info.Gid = Guid.NewGuid().ToString();
                info.Age = IdCardHelper.GetAgeFromIdCard(info.IdCardNo);
                info.Birthday = IdCardHelper.GetBirthDayFromIdCard(info.IdCardNo);
                info.sys_CreateDate = DateTime.Now;
                _jzPersonRep.Insert(info);
            }
            else
            {
                var model = _jzPersonRep.AsQueryable()
                      .Where(a => a.IdCardNo == info.IdCardNo && a.IsSaved == 0 && a.GroupId == info.GroupId && a.Gid != info.Gid).First();


                if (model != null)
                {
                    throw Oops.Bah("该项目中此身份证号已存在");
                }
                if (!info.GroupId.Equals(AppStr.NEWGROUPID) && info.OrderIndex == 0)
                {
                    var orderIndex = _jzPersonRep.AsQueryable()
                                      .Where(m => m.GroupId == info.GroupId && m.Town == info.Town)
                                      .Max(m => m.OrderIndex);
                    info.OrderIndex = (orderIndex + 1);
                }
                info.Age = IdCardHelper.GetAgeFromIdCard(info.IdCardNo);
                info.Birthday = IdCardHelper.GetBirthDayFromIdCard(info.IdCardNo);
                info.Sex = IdCardHelper.GetSexFromIdCard(info.IdCardNo);
                _jzPersonRep.Update(info);
                cts_JZInfo cinfo = _jzInfoRep.GetFirst(m => m.DBPersonId == info.Gid);
                if (cinfo!=null)
                {
                    cinfo.Town = info.Town;
                    cinfo.Name = info.Name;
                    cinfo.Address = info.Address;
                    cinfo.IdCardNo = info.IdCardNo;
                    _jzInfoRep.Update(cinfo);
                }
            }


            cts_GroupTown cT = new cts_GroupTown
            { 
                GroupId = info.GroupId,
                TownName = info.Town,
                Type=2
            };
            var town = _groupTownRep.GetFirst(m => m.GroupId == cT.GroupId && m.TownName.Equals(cT.TownName));
            if (town == null)
            {
                _groupTownRep.Insert(cT);
            }
             
        }

        /// <summary>
        /// 删除核查对象
        /// </summary>
        /// <param name="gid"></param>
        public void DeleteJZPerson(string gid)
        {
            _jzPersonRep.AsUpdateable().SetColumns(m => m.sys_IsDelete == 1).Where(m => m.Gid == gid).ExecuteCommand();
            _jzInfoRep.AsUpdateable().SetColumns(m => m.sys_IsDelete == 1).Where(m => m.DBPersonId == gid).ExecuteCommand();

        }

        /// <summary>
        /// 删除核查分组
        /// </summary>
        /// <param name="gid"></param>

        public void DeleteJZGroup(string gid)
        {
            _jzPersonRep.AsUpdateable().SetColumns(m => m.sys_IsDelete == 1).Where(m => m.GroupId == gid).ExecuteCommand();
            _jzGroupRep.AsUpdateable().SetColumns(m => m.sys_IsDelete == 1).Where(m => m.Gid == gid).ExecuteCommand();

        }
        /// <summary>
        /// 修改图片旋转角度
        /// </summary>
        /// <param name="input"></param>
        public void UpdateImgRotate(UpdateImgRotateInput input)
        {
            var info = _JZInfoImgRep.GetFirst(m => m.Gid == input.gid);
            if (info == null)
            {
                throw Oops.Bah("未找到此图片");
            } 
            info.Rotate += input.rotate;
            while (info.Rotate >= 360)
            {
                info.Rotate -= 360;
            } while (info.Rotate < 0)
            {
                info.Rotate += 360;
            }
            _JZInfoImgRep.Update(info);
        }

        /// <summary>
        /// 存档核查项目
        /// </summary>
        /// <param name="input"></param>
        public void SavedJZGroup(SavedPersonGroupInput input)
        {
            _jzGroupRep.AsUpdateable().SetColumns(m => new cts_JZGroup { SavedAreaId = input.areaid, IsSaved = 1 }).Where(m => m.Gid == input.gid).ExecuteCommand();
            _jzPersonRep.AsUpdateable().SetColumns(m => m.IsSaved == 1).Where(m => m.GroupId == input.gid).ExecuteCommand();
            _jzInfoRep.AsUpdateable().SetColumns(m => m.IsSaved == 1).Where(m => SqlFunc.Subqueryable<cts_JZPerson>()
               .Where(p => p.GroupId == input.gid)
               .Select(p => p.Gid)
               .Contains(m.DBPersonId)).ExecuteCommand(); 
            
        }



        //查询救助核查工作量详情
        public SqlSugarPagedXList<WorkStaticDetailDto> SearchJZWorkDetail(ReportDto input)
        {
            if (string.IsNullOrEmpty(input.sdate))
            {
                input.sdate = "2015-01-01";
            }
            if (string.IsNullOrEmpty(input.edate))
            {
                input.edate = DateTime.Now.ToString("yyyy-MM-dd");
            }
            var sql = $@"SELECT A.sys_Creater,A.sys_CreaterName,
CONVERT(varchar(100),A.sys_CreateDate,20) AS sys_CreateDate,B.Gid,B.Name,C.GroupName FROM  cts_JZInfo A 
INNER JOIN cts_JZPerson B ON A.DBPersonId=B.Gid
INNER JOIN cts_JZGroup C ON B.GroupId=C.Gid
 WHERE B.sys_IsDelete=0   AND (A.sys_Creater='{input.empid}' OR B.Gid  in (SELECT DISTINCT JZInfoId FROM cts_JZInfoImg WHERE Type<7  AND [sys_Creater] = '{input.empid}')  ) AND DATEDIFF(DAY,'{input.sdate}',A.sys_CreateDate)>=0 AND DATEDIFF(DAY,A.sys_CreateDate,'{input.edate}')>=0";

            return _jzPersonRep.AsSugarClient().SqlQueryable<WorkStaticDetailDto>(sql).ToPagedList(input.page, input.size); ;

        }

        /// <summary>
        /// 查询救助核查工作量详情
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public dynamic SearchJZWorkDetailByDate(ReportDto input)
        {
            if (string.IsNullOrEmpty(input.sdate))
            {
                input.sdate = "2015-01-01";
            }
            if (string.IsNullOrEmpty(input.edate))
            {
                input.edate = DateTime.Now.ToString("yyyy-MM-dd");
            }
            var sqlstr = $@"SELECT sys_CreaterName,sys_CreateDate,SUM(JZCount) AS JZCount FROM (

SELECT  B.sys_CreaterName, CONVERT(varchar(100), B.sys_CreateDate, 23) AS sys_CreateDate,SUM(0.5) AS JZCount FROM cts_JZPerson A INNER JOIN cts_JZInfo B ON A.Gid=B.DBPersonId
 WHERE A.sys_IsDelete=0 
 AND B.sys_Creater='{input.empid}' AND DATEDIFF(DAY,'{input.sdate}',B.sys_CreateDate)>=0 AND DATEDIFF(DAY,B.sys_CreateDate,'{input.edate}')>=0
 GROUP BY B.sys_CreaterName,CONVERT(varchar(100), B.sys_CreateDate, 23)
 UNION ALL
 SELECT sys_CreaterName,sys_CreateDate,SUM(JZCount) AS JZCount FROM
(
SELECT A.*,CONVERT(varchar(100), B.sys_CreateDate, 23) AS sys_CreateDate,C.PersonCount,CAST( 0.5/PersonCount as decimal(18,2)) AS JZCount FROM (
 SELECT distinct B.sys_Creater,B.sys_CreaterName,B.DBInfoId AS PersonId FROM cts_JZPerson A INNER JOIN  [cts_JZInfoImg] B ON A.Gid=B.DBInfoId
 WHERE A.sys_IsDelete=0 AND B.Type <7 AND B.sys_Creater='{input.empid}' AND DATEDIFF(DAY,'{input.sdate}',B.sys_CreateDate)>=0 AND DATEDIFF(DAY,B.sys_CreateDate,'{input.edate}')>=0
 ) A 
 INNER JOIN cts_JZInfo B  ON A.PersonId=B.DBPersonId
 INNER JOIN (
        SELECT PersonId,COUNT(0) AS PersonCount FROM
                (
                    SELECT distinct B.sys_Creater,B.sys_CreaterName,B.DBInfoId AS PersonId FROM cts_JZPerson A INNER JOIN  [cts_JZInfoImg] B ON A.Gid=B.DBInfoId
                    WHERE A.sys_IsDelete=0 AND B.Type <7  AND DATEDIFF(DAY,'{input.sdate}',B.sys_CreateDate)>=0 AND DATEDIFF(DAY,B.sys_CreateDate,'{input.edate}')>=0
                )t  GROUP BY PersonId
     ) C  ON A.PersonId=C.PersonId WHERE DATEDIFF(DAY,'{input.sdate}',B.sys_CreateDate)>=0 AND DATEDIFF(DAY,B.sys_CreateDate,'{input.edate}')>=0
 )T GROUP BY sys_CreateDate,sys_CreaterName
 ) T
 GROUP BY sys_CreaterName,sys_CreateDate  ORDER BY sys_CreateDate";


            return _jzPersonRep.AsSugarClient().SqlQueryable<dynamic>(sqlstr).ToList();
        }

        /// <summary>
        /// 查询救助文档
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        


    }


}
